from config import *
from common import *
import sqlite3
from twisted.internet import reactor
class RecipeServer():            
    schema_file = 'recipe_server/recipe_server.sql'    
    db = 'recipe_server/recipe_server.sqlite'
    crypto_db = 'recipe_server/crypto.sqlite'
    crypto_schema_file = 'recipe_server/crypto.sql' 
    _stopped = False 
    local_recipes = []        
    sql_results = []
    def __init__(self,
                 rpc_host=RECIPE_SERVER_RPC_HOST,
                 rpc_port=RECIPE_SERVER_RPC_PORT,
                 external_ip=RECIPE_SERVER_EXTERNAL_IP,
                 external_port=RECIPE_SERVER_EXTERNAL_PORT):                           
        self.rpc_host = rpc_host
        self.rpc_port = rpc_port
        self.external_ip = external_ip
        self.external_port = external_port
        check_schema(self.db, self.schema_file)
        check_schema(self.crypto_db, self.crypto_schema_file)
        crypto.generate_keys(self.crypto_db)
        self.update_local_recipes()
        rpc_server = RPCServer(self)
    def run_sql(self, query):
        print "running sql",query
        con = sqlite3.connect(self.db)
        res = con.execute(query)
        sql_results = []
        for row in res:
            row = map(str, row)
            sql_results.append(row)
        self.sql_results = sql_results
        print "sql returned %d results" % len(sql_results)  
        con.commit()
    ######### RPC METHODS ##################3
    def search(self, query):
        print "search"
        con = sqlite3.connect(self.db)
        ret = []
        file_ids = []
        file_infos = []
        block_sizes = []
        res = con.execute('select * from recipes where name like "%%%s%%" or file_id="%s"' % (query,query))
        for row in res: 
            file_ids.append(row[0])
            file_infos.append(FileInfo(row[1], row[2], row[3]))
            block_sizes.append(row[4])
        for i in range(len(file_ids)):
            res = con.execute('select block_id1, block_id2 from recipe_blocks where file_id="%s"' % file_ids[i])
            xids = []
            yids = []
            for row in res:
                xids.append(row[0])
                yids.append(row[1])
            ret.append(Recipe(file_ids[i], file_infos[i],xids, yids, block_sizes[i]))
        return ret        
        
    def get_public_key(self):
        print "get_public_key"
        return crypto.get_public_key(self.crypto_db)
    
    def get_recipe(self, file_id):
        print "get_recipe"
        con = sqlite3.connect(self.db)
        res = con.execute('select * from recipes where file_id = "%s"' % file_id)
        row = res.fetchone()
        if not row:
            print "no recipe with file_id = ", file_id
        file_info = FileInfo(row[1], row[2], row[3])
        block_size = row[4]
        res = con.execute('select block_id1, block_id2 from recipe_blocks where file_id="%s"' % file_id)        
        xids = []
        yids = []
        for row in res:
            xids.append(row[0])
            yids.append(row[1])
        print "returning recipe with #xids = ",len(xids)," #yids =",len(yids)
        recipe = Recipe(file_id, file_info, xids, yids, block_size) 
        if len(xids) -1 != (file_info.size - 1)/block_size:
            print "the number of blocks in recipe doesn't match the number of blocks in db"
            print "len(xids) = ", len(xids)
            print "block_size = ", block_size
            print "file_info.size = ", file_info.size
        return recipe
    def add_recipe_info(self, fileid, filename, size, category, block_size):
        print "add_recipe_info"
        con = sqlite3.connect(self.db)
        con.execute("insert or ignore into recipes values (?,?,?,?,?)", 
                    (fileid, filename, size, category, block_size))            
        con.commit()
    
    def add_recipe_blocks(self, fileid, xids, yids):
        print "add_recipe_blocks fileid = ",fileid
        con = sqlite3.connect(self.db)
        for x,y in zip(xids,yids):
            con.execute('insert into recipe_blocks values (?, ?, ?)', (fileid, x, y))
        con.commit()
    
    def register_recipe(self, recipe, con=None):
        print "adding recipe"        
        fi = recipe.file_info
        if len(recipe.xids) != fi.size:
            print "the number of blocks in xids is not consistent with the block size"
            print "num_blocks = ", fi.size
            print "len(xids) = ", len(recipe.xids)
        t = (recipe.file_id, fi.name, fi.size, fi.category, recipe.block_size)
        commit = False
        if not con:
            con = sqlite3.connect(self.db)
            commit = True
        print "executing insert statement"
        con.execute('insert or ignore into recipes values (?,?,?,?,?)', t)
        print "deleting the previous blocks of recipe with id", recipe.file_id        
        con.execute('delete from recipe_blocks where file_id="%s"' % recipe.file_id)
        for x,y in zip(recipe.xids,recipe.yids):
            con.execute('insert into recipe_blocks values (?, ?, ?)', (recipe.file_id, x, y))
        if commit:
            con.commit()
        print "recipe added"
        return True
    def register_recipes(self, recipes):
        print "adding recipe"        
        con = sqlite3.connect(self.db)
        for recipe in recipes:
            self.register_recipe(recipe, con)
        con.commit()        
        return True
    
    def update_local_recipes(self):     
        #print "get_local_recipes"   
        con = sqlite3.connect(self.db)
        c = con.execute('select * from recipes')
        self.local_recipes = [(row[0], row[1], row[2], (row[2]-1)/row[4] + 1) for row in c]
        reactor.callLater(2, self.update_local_recipes)                                
    
    def reset(self):
        print "RESET"
        print "resetting the peer internals"
        self.local_recipes  = []            
        print "clearing the databases"
        drop_tables(self.db)
        create_tables(self.db, self.schema_file)
        print "DONE RESET"
